package com.ms.server.init;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.system.comm.utils.FrameMd5Util;
import com.system.dao.BaseDao;
import com.system.dao.model.TableField;
import com.system.dao.utils.DbUtil;
import com.system.ds.DbContextHolder;

/**
 * 脚本升级
 * @author yuejing
 * @date 2018年12月18日 上午11:17:25
 */
public class DbUpdate {

	private static final Logger LOGGER = LoggerFactory.getLogger(DbUpdate.class);

	/**
	 * 初始化更新后的脚本
	 */
	public static void init() {
		/*ThreadPoolTaskExecutor task = FrameSpringBeanUtil.getBean(ThreadPoolTaskExecutor.class);
		task.execute(new Runnable() {

			@Override
			public void run() {*/
		updateSql();
		/*}

		});*/
	}
	private static void updateSql() {
		DbContextHolder.setDsName("dataSource1");
		BaseDao baseDao = new BaseDao();
		if (DbUtil.isMysql()) {
			version1_0_0(baseDao);
			version1_0_1(baseDao);
		} else if (DbUtil.isOracle()) {
			// TODO 带扩展Oracle的初始化脚本
		}

	}
	private static void version1_0_0(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from sys_user limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_user]: " + e.getMessage());
			String sql = "create table sys_user\r\n" + 
					"(\r\n" + 
					"   id                   int not null auto_increment comment '编号',\r\n" + 
					"   username             varchar(30) not null comment '用户名',\r\n" + 
					"   password             varchar(80) not null comment '密码',\r\n" + 
					"   nickname             varchar(30) not null comment '昵称',\r\n" + 
					"   addtime              datetime not null comment '添加时间',\r\n" + 
					"   adduser              int not null comment '添加人',\r\n" + 
					"   status               int not null comment '状态【0正常、1冻结】',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table sys_user comment '用户表'";
			baseDao.updateSql(sql);
			sql = "create unique index unique_username on sys_user\r\n" + 
					"(\r\n" + 
					"   username\r\n" + 
					")";
			baseDao.updateSql(sql);
			String pwd = FrameMd5Util.getInstance().encodePassword("123456", "1");
			sql = "INSERT INTO `sys_user`(`id`,`username`,`password`,`nickname`,`addtime`,`adduser`,`status`)\r\n" + 
					" VALUES (1,'admin','" + pwd + "','管理员',now(),1,0)";
			baseDao.saveSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from sys_config limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_config]: " + e.getMessage());
			String sql = "create table sys_config\r\n" + 
					"(\r\n" + 
					"   id                   int not null auto_increment comment '编号',\r\n" + 
					"   code                 varchar(50) not null comment '编码',\r\n" + 
					"   name                 varchar(50) not null comment '名称',\r\n" + 
					"   value                varchar(100) not null comment '值',\r\n" + 
					"   remark               varchar(100) comment '描叙',\r\n" + 
					"   exp1                 varchar(100) comment '扩展1',\r\n" + 
					"   exp2                 varchar(100) comment '扩展2',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table sys_config comment '系统配置表'";
			baseDao.updateSql(sql);

			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (1,'task.main.cron','主线程的时间表达式','0/8 * * * * ?',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (2,'mail.smtp','发送邮箱的smtp','smtp.163.com',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (3,'mail.from','发送邮件的邮箱','xxx@163.com',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (4,'mail.username','发送邮件的用户名','xxxx',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (5,'mail.password','发送邮件的密码','xxxxxx',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (6,'joblog.save.day','调度记录保存天数','30',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (7,'clean.cron','清空调度记录表达式','0 0 23 * * ?',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (8,'serv.save.day','已停止的服务保存天数','30',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (9,'lock.destroy.time','消耗服务和任务的时间[单位:s]','15',default,default,default)";
			baseDao.updateSql(sql);
			sql = "INSERT INTO `sys_config`(`id`,`code`,`name`,`value`,`remark`,`exp1`,`exp2`)\r\n" + 
					" VALUES (10,'leader.cron','Leader的时间表达式','0/5 * * * * ?',default,default,default)";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from serv_info limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[serv_info]: " + e.getMessage());
			String sql = "create table serv_info\r\n" + 
					"(\r\n" + 
					"   servid               varchar(36) not null comment '编号',\r\n" + 
					"   ip                   varchar(30) not null comment 'ip地址',\r\n" + 
					"   updatetime           datetime not null comment '更新时间',\r\n" + 
					"   addtime              datetime not null comment '添加时间',\r\n" + 
					"   status               int not null comment '状态[10正常、20已销毁]',\r\n" + 
					"   isleader             int not null default 0 comment '是否Leader[0否、1是]',\r\n" + 
					"   primary key (servid)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table serv_info comment '服务表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from serv_eq limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[serv_eq]: " + e.getMessage());
			String sql = "create table serv_eq\r\n" + 
					"(\r\n" + 
					"   id                   int not null auto_increment comment '编号',\r\n" + 
					"   servid               varchar(36) not null comment '服务编号',\r\n" + 
					"   jobid                int not null comment '任务编号',\r\n" + 
					"   status               int not null comment '状态[10待释放、20已释放]',\r\n" + 
					"   addtime              datetime not null comment '添加时间',\r\n" + 
					"   destroytime          datetime comment '释放时间',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table serv_eq comment '服务均衡表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from task_project limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[task_project]: " + e.getMessage());
			String sql = "create table task_project\r\n" + 
					"(\r\n" + 
					"   id                   int not null auto_increment comment '编号',\r\n" + 
					"   name                 varchar(30) not null comment '名称',\r\n" + 
					"   remark               varchar(200) comment '描述',\r\n" + 
					"   isrecemail           int not null comment '接收邮件（0否1是）',\r\n" + 
					"   recemail             varchar(100) comment '接收邮箱（多个,分隔）',\r\n" + 
					"   addtime              datetime not null comment '添加时间',\r\n" + 
					"   adduser              int not null comment '添加人',\r\n" + 
					"   sign                 varchar(200) comment '签名规则',\r\n" + 
					"   signstring           varchar(300) comment '签名参数与内容',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table task_project comment '项目表'";
			baseDao.updateSql(sql);
			sql = "create unique index unique_name on task_project\r\n" + 
					"(\r\n" + 
					"   name\r\n" + 
					")";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from task_job limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[task_job]: " + e.getMessage());
			String sql = "create table task_job\r\n" + 
					"(\r\n" + 
					"   id                   int not null auto_increment comment '编号',\r\n" + 
					"   projectid            int not null comment '项目编号',\r\n" + 
					"   name                 varchar(30) not null comment '名称',\r\n" + 
					"   remark               varchar(200) comment '描叙',\r\n" + 
					"   link                 varchar(200) not null comment '调用链接',\r\n" + 
					"   cron                 varchar(50) not null comment '执行规则',\r\n" + 
					"   isfailmail           int not null comment '失败发邮件（0否1是）',\r\n" + 
					"   addtime              datetime not null comment '添加时间',\r\n" + 
					"   adduser              int not null comment '添加人',\r\n" + 
					"   status               int not null comment '状态【枚举JobStatus】',\r\n" + 
					"   statusmsg            varchar(200) comment '状态消息',\r\n" + 
					"   servid               varchar(36) comment '服务编号',\r\n" + 
					"   updatetime           datetime not null comment '更新时间',\r\n" + 
					//"   calltype             int not null comment '调用方式[10http或https/20微服务]',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table task_job comment '任务表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from task_job_log limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[task_job_log]: " + e.getMessage());
			String sql = "create table task_job_log\r\n" + 
					"(\r\n" + 
					"   id                   int not null auto_increment comment '编号',\r\n" + 
					"   jobid                int not null comment '任务编号',\r\n" + 
					"   addtime              datetime not null comment '添加时间',\r\n" + 
					"   status               int not null comment '状态',\r\n" + 
					"   link                 varchar(300) not null comment '请求地址',\r\n" + 
					"   result               longtext not null comment '调度返回结果',\r\n" + 
					"   servicecode          varchar(50) comment '服务编号',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table task_job_log comment '任务调度记录表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

	}
	private static void version1_0_1(BaseDao baseDao) {
		String sql = null;
		// 任务表增加字段: 失败发送邮件规则
		List<TableField> fields = baseDao.tableFields("task_job");
		boolean isFailemailrule = false;
		for (TableField f : fields) {
			if ("failemailrule".equalsIgnoreCase(f.getName())) {
				isFailemailrule = true;
			}
		}
		if (!isFailemailrule) {
			LOGGER.info("|===================================================");
			LOGGER.info("|========== 表[task_job]增加字段: failemailrule");
			sql = "alter table task_job add failemailrule varchar(250) comment '失败发送邮件规则'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

	}
}
